/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package isd.resto.dao;

import isd.facturacion.modelo.FC_ItemAplicacion;
import isd.facturacion.modelo.FC_ItemMovimientoPK;
import isd.general.dao.BaseDAO;
import isd.ventas.modelo.VT_Cliente;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import javax.ejb.Stateless;
import javax.ejb.LocalBean;
import javax.persistence.NoResultException;
import javax.persistence.Query;
import isd.facturacion.modelo.FC_ItemProducto;
import isd.facturacion.modelo.FC_Movimiento;
import isd.facturacion.modelo.FC_MovimientoPK;
import isd.facturacion.vista.ComprobantesPendientes;
import isd.facturacion.vista.PedidoProducto;
import isd.facturacion.vista.ProductoPendiente;
import isd.resto.modelo.RT_EstadoMovimiento;
import isd.resto.modelo.RT_Movimiento;
import java.util.Date;

/**
 *
 * @author Claudio
 */
@Stateless
@LocalBean
public class RT_MovimientoDAO extends BaseDAO {

    public RT_Movimiento getMovimiento(int id){
        return getObjeto(RT_Movimiento.class, id);
    }

    public List<RT_Movimiento> getMovimientos(Map<String,String> filtro) {

        try {
            String sQuery = "SELECT m FROM MovimientoFacturacion m ";
                   sQuery += generarStringFiltro(filtro,"m", true);
                   sQuery +=" ORDER BY m.fchmov DESC";

            Query q = em.createQuery(sQuery);            
            return q.getResultList();
            
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error al obtener movimientos de facturacion");
            return new ArrayList<RT_Movimiento>();
        }
    }

    public List<RT_Movimiento> getMovimientos(Date fechaMovimiento, RT_EstadoMovimiento estado) {

        try {
            String sQuery = "SELECT m FROM RT_Movimiento m "
                    + "WHERE m.estado.codigo = :estado "
                    + "AND m.fechaMovimiento = :fechaMovimiento ";
                    

            Query q = em.createQuery(sQuery);

            q.setParameter("estado", estado.getCodigo());
            q.setParameter("fechaMovimiento", fechaMovimiento);
            return q.getResultList();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error al obtener movimientos no cerrados");
            return new ArrayList<RT_Movimiento>();
        }


    }

    public List<FC_Movimiento> getCarritosConfirmados(VT_Cliente cliente) {

        try {
            String sQuery = "SELECT m FROM MovimientoFacturacion m "
                    + "WHERE m.cliente.nrocta = '" + cliente.getNrocta() + "' "
                    + "AND m.circom = '0110' "
                    + "AND m.cirapl = '0110' "
                    + "AND (m.estaut = '1') "
                    + "AND m.debaja = 'N' "
                    + "ORDER BY m.fchmov DESC";

            Query q = em.createQuery(sQuery);
            return q.getResultList();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error al obtener carritos confirmados");
            return new ArrayList<FC_Movimiento>();
        }
    }

    public FC_Movimiento getMovimientoFacturacionById(FC_MovimientoPK id){
        try {
            return em.find(FC_Movimiento.class, id);
        } catch (Exception e) {
            System.out.println("No se encontró movimiento: "+ id);
            return null;
        }
    }

    public List<FC_ItemProducto> getItemMovimientoFacturacion(FC_MovimientoPK id){

        try {
            String sQuery = "SELECT i FROM ItemProductoFacturacion i "
                    + "WHERE i.itemMovimientoVentaPK.modfor = '" + id.getModfor() + "' "
                    + "AND i.itemMovimientoVentaPK.codfor = '" + id.getCodfor() + "' "
                    + "AND i.itemMovimientoVentaPK.nrofor = " + id.getNrofor() + " "
                    + "AND i.itemMovimientoVentaPK.modfor = p.itemMovimientoVentaPK.modapl "
                    + "AND i.itemMovimientoVentaPK.codfor = p.itemMovimientoVentaPK.codapl "
                    + "AND i.itemMovimientoVentaPK.nrofor = p.itemMovimientoVentaPK.nroapl "
                    + "ORDER BY i.fchmov DESC";

            Query q = em.createQuery(sQuery);
            return q.getResultList();
            
        } catch (Exception e) {
            System.out.println("No se encontraron items movimiento: "+ id);
            return new ArrayList<FC_ItemProducto>();
        }
    }

    public List<ComprobantesPendientes> getComprobantesPendientes(Map<String,String> filtro) {

        try {

            String sQuery = " SELECT CONVERT(VARCHAR(3), I.FCRMVI_CODAPL) + CONVERT(VARCHAR, I.FCRMVI_NROAPL) AS ID, "
                    + " I.FCRMVI_MODAPL AS MODFOR, I.FCRMVI_CODAPL AS CODFOR, I.FCRMVI_NROAPL AS NROFOR, C.FCRMVH_FCHMOV AS FCHMOV, "
                    + " C.FCRMVH_NROCTA AS NROCTA, SUM(I.FCRMVI_CANTID) AS ITEMS_PENDIENTES, C.FCRMVH_SUCURS AS SUCURS, "
                    + " C.FCRMVH_CIRCOM AS CIRCOM, C.FCRMVH_ESTAUT AS ESTAUT "
                    + " FROM  dbo.FCRMVI AS I INNER JOIN "
                    + " dbo.FCRMVH AS C ON I.FCRMVI_MODAPL = C.FCRMVH_MODFOR AND I.FCRMVI_CODAPL = C.FCRMVH_CODFOR AND I.FCRMVI_NROAPL = C.FCRMVH_NROFOR "
                    + " WHERE FCRMVH_NROCTA = ?1 "
                    + " AND FCRMVH_CIRCOM = ?2 "
                    + " GROUP BY I.FCRMVI_MODAPL, I.FCRMVI_CODAPL, I.FCRMVI_NROAPL, C.FCRMVH_FCHMOV, C.FCRMVH_NROCTA, "
                    + " C.FCRMVH_SUCURS, C.FCRMVH_CIRCOM, C.FCRMVH_ESTAUT "
                    + " HAVING (SUM(I.FCRMVI_CANTID) > 0) ORDER BY FCRMVH_FCHMOV DESC ";

            /**
            String sQuery = "SELECT p FROM ComprobantesPendientes p ";
            sQuery += generarStringFiltro(filtro,"p", true);
            sQuery +=" ORDER BY p.fchmov DESC";
            */


            Query q = em.createNativeQuery(sQuery, ComprobantesPendientes.class);

            q.setParameter(1, filtro.get("nrocta"));
            q.setParameter(2, filtro.get("circom"));

            return q.getResultList();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error al obtener comprobantes pendientes");
            return new ArrayList<ComprobantesPendientes>();
        }        
    }

    public BigDecimal getPendienteItem(FC_ItemProducto i) {

        try { String sqlQuery = "SELECT ISNULL(SUM(I.FCRMVI_CANTID),0) "
                + "FROM dbo.FCRMVI AS I INNER JOIN dbo.FCRMVH AS C ON I.FCRMVI_MODAPL = C.FCRMVH_MODFOR "
                + "                                 AND I.FCRMVI_CODAPL = C.FCRMVH_CODFOR "
                + "                                 AND I.FCRMVI_NROAPL = C.FCRMVH_NROFOR "
                + "WHERE I.FCRMVI_MODAPL = 'FC' "
                + "AND I.FCRMVI_CODAPL = '"+i.getCodapl()+"' "
                + "AND I.FCRMVI_NROAPL =  "+i.getNroapl()+" "
                + "AND I.FCRMVI_ITMAPL =  "+i.getItmapl()+" "
                + "AND I.FCRMVI_TIPORI = '"+i.getProducto().getTippro()+"' "
                + "AND I.FCRMVI_ARTORI = '"+i.getProducto().getArtcod()+"' "
                + "GROUP BY "
                + "I.FCRMVI_MODAPL, "
                + "I.FCRMVI_CODAPL, "
                + "I.FCRMVI_NROAPL, "
                + "I.FCRMVI_TIPORI,"
                + "I.FCRMVI_ARTCOD "
                + "HAVING SUM(I.FCRMVI_CANTID) > 0 ";

            Query q = em.createNativeQuery(sqlQuery);            
            return (BigDecimal) q.getSingleResult(); 

        } catch(NoResultException nre){
            
            return BigDecimal.ZERO;

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error al obtener pendiente por item");
            return BigDecimal.ZERO;
        }       
    }

    public List<FC_Movimiento> getPedidosFacturados(VT_Cliente cliente) {
        try {
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.DAY_OF_YEAR, -730);


            String sQuery = "SELECT m FROM MovimientoFacturacion m "
                    + "WHERE m.cliente.nrocta = '" + cliente.getNrocta() + "' "
                    + "AND ((m.circom = '0400') OR (m.circom = '0410') OR (m.circom = '0450')) "
                    + "AND m.debaja = 'N' "
                    + "ORDER BY m.fchmov DESC";

            Query q = em.createQuery(sQuery);
            q.setMaxResults(50);
            return q.getResultList();

        } catch (Exception e) {
            System.err.println("Error al obtener pedidos facturados");
            return new ArrayList<FC_Movimiento>();
        }
    }

    public List<ProductoPendiente> getProductosPendientes(VT_Cliente cliente) {
        try {
            
            String sQuery = " SELECT dbo.FCRMVH.FCRMVH_NROCTA + dbo.FCRMVI.FCRMVI_TIPORI + dbo.FCRMVI.FCRMVI_ARTORI AS ID, "
                    + " dbo.FCRMVH.FCRMVH_NROCTA AS NROCTA, dbo.FCRMVI.FCRMVI_TIPORI AS TIPPRO, dbo.FCRMVI.FCRMVI_ARTORI AS ARTCOD, "
                    + " (SELECT     STMPDH_DESCRP FROM dbo.STMPDH "
                    + "                            WHERE      (STMPDH_TIPPRO = dbo.FCRMVI.FCRMVI_TIPORI) AND (STMPDH_ARTCOD = dbo.FCRMVI.FCRMVI_ARTORI)) AS DESCRIPCION, "
                    + "                          (SELECT     STMPDH_INDCOD "
                    + "                            FROM          dbo.STMPDH AS STMPDH_1 "
                    + "                             WHERE      (STMPDH_TIPPRO = dbo.FCRMVI.FCRMVI_TIPORI) AND (STMPDH_ARTCOD = dbo.FCRMVI.FCRMVI_ARTORI)) AS INDCOD,  "
                    + "                      ISNULL(dbo.FCRMVI.FCRMVI_PRECIO, 0) AS PRECIO, ISNULL "
                    + "                          ((SELECT     GRCCOI_PORCEN "
                    + "                              FROM         dbo.GRCCOI "
                    + "                              WHERE     (GRCCOI_MODCPT = dbo.FCRMVI.FCRMVI_MODCPT) AND (GRCCOI_TIPCPT = dbo.FCRMVI.FCRMVI_TIPCPT) AND (GRCCOI_TIPIMP = 'IVA') AND  "
                    + "                                                    (GRCCOI_CODCPT = dbo.FCRMVI.FCRMVI_CODCPT)), 0) AS IVA, SUM(dbo.FCRMVI.FCRMVI_CANTID) AS PENDIENTE,  "
                    + "                      SUM(dbo.FCRMVI.FCRMVI_CANTID * dbo.FCRMVI.FCRMVI_PRECIO + dbo.FCRMVI.FCRMVI_CANTID * dbo.FCRMVI.FCRMVI_PRECIO * dbo.FCRMVI.FCRMVI_PCTBFN / 100) "
                    + "                       AS IMPORTE_TOTAL "
                    + " FROM         dbo.FCRMVH INNER JOIN "
                    + "                      dbo.FCRMVI ON dbo.FCRMVH.FCRMVH_MODFOR = dbo.FCRMVI.FCRMVI_MODAPL AND dbo.FCRMVH.FCRMVH_CODFOR = dbo.FCRMVI.FCRMVI_CODAPL AND  "
                    + "                      dbo.FCRMVH.FCRMVH_NROFOR = dbo.FCRMVI.FCRMVI_NROAPL "
                    + " WHERE     (dbo.FCRMVH.FCRMVH_CIRCOM = '0200') "
                    + " AND (FCRMVH_NROCTA = ?1 ) "
                    + " GROUP BY dbo.FCRMVH.FCRMVH_NROCTA, dbo.FCRMVI.FCRMVI_TIPORI, dbo.FCRMVI.FCRMVI_ARTORI, "
                    + " dbo.FCRMVI.FCRMVI_MODCPT, dbo.FCRMVI.FCRMVI_TIPCPT, dbo.FCRMVI.FCRMVI_CODCPT, dbo.FCRMVI.FCRMVI_PRECIO"
                    + " HAVING (SUM(dbo.FCRMVI.FCRMVI_CANTID) > 0) "
                    + " ORDER BY NROCTA, TIPPRO, ARTCOD, PRECIO ";

            Query q = em.createNativeQuery(sQuery, ProductoPendiente.class);

            q.setParameter(1, cliente.getNrocta());
            return q.getResultList();

                    /**
                    String sQuery = "SELECT p FROM ProductoPendiente p "
                    + "WHERE p.nrocta = :nrocta "
                    + "ORDER BY p.pendiente DESC";


                    Query q = em.createQuery(sQuery);
                    q.setParameter("nrocta", cliente.getNrocta());
                    return q.getResultList();
                     *  */

        } catch (Exception e) {
            System.err.println("Error al obtener productos pendientes");
            return new ArrayList<ProductoPendiente>();
        }
    }

    public ProductoPendiente getPendientePorProducto(String tipo, String codigo, String nrocta) {

        try {
            String sQuery = "SELECT FCRMVH.FCRMVH_NROCTA + dbo.FCRMVI.FCRMVI_TIPORI + dbo.FCRMVI.FCRMVI_ARTORI AS ID, "
                    + " FCRMVH.FCRMVH_NROCTA AS NROCTA, FCRMVI.FCRMVI_TIPORI AS TIPPRO, FCRMVI.FCRMVI_ARTORI AS ARTCOD, "
                    + " (SELECT STMPDH_DESCRP FROM STMPDH  WHERE (STMPDH_TIPPRO = FCRMVI.FCRMVI_TIPORI) AND (STMPDH_ARTCOD = FCRMVI.FCRMVI_ARTORI)) AS DESCRIPCION, "
                    + " (SELECT STMPDH_INDCOD FROM STMPDH AS STMPDH_1 WHERE (STMPDH_TIPPRO = FCRMVI.FCRMVI_TIPORI) AND (STMPDH_ARTCOD = FCRMVI.FCRMVI_ARTORI)) AS INDCOD,  "
                    + " ISNULL(FCRMVI.FCRMVI_PRECIO, 0) AS PRECIO, "
                    + " ISNULL((SELECT GRCCOI_PORCEN "
                    + "         FROM GRCCOI "
                    + "         WHERE (GRCCOI_MODCPT = dbo.FCRMVI.FCRMVI_MODCPT) AND (GRCCOI_TIPCPT = dbo.FCRMVI.FCRMVI_TIPCPT) "
                    + "         AND (GRCCOI_TIPIMP = 'IVA') AND (GRCCOI_CODCPT = dbo.FCRMVI.FCRMVI_CODCPT)), 0) AS IVA, "
                    + " SUM(dbo.FCRMVI.FCRMVI_CANTID) AS PENDIENTE, "
                    + " SUM(FCRMVI.FCRMVI_CANTID * FCRMVI.FCRMVI_PRECIO + FCRMVI.FCRMVI_CANTID * FCRMVI.FCRMVI_PRECIO * FCRMVI.FCRMVI_PCTBFN / 100)AS IMPORTE_TOTAL "
                    + " FROM FCRMVH INNER JOIN FCRMVI "
                    + "  ON FCRMVH.FCRMVH_MODFOR  = FCRMVI.FCRMVI_MODAPL "
                    + "  AND FCRMVH.FCRMVH_CODFOR = FCRMVI.FCRMVI_CODAPL "
                    + "  AND FCRMVH.FCRMVH_NROFOR = FCRMVI.FCRMVI_NROAPL "
                    + " WHERE (FCRMVH.FCRMVH_CIRCOM = '0200') "
                    + " AND FCRMVH.FCRMVH_NROCTA = '"+nrocta +"' "
                    + " AND FCRMVI.FCRMVI_TIPPRO = '"+tipo+"' "
                    + " AND FCRMVI.FCRMVI_ARTCOD = '"+codigo+"' "
                    + " GROUP BY FCRMVH.FCRMVH_NROCTA, FCRMVI.FCRMVI_TIPORI, "
                    + " FCRMVI.FCRMVI_ARTORI, FCRMVI.FCRMVI_MODCPT, FCRMVI.FCRMVI_TIPCPT, "
                    + " FCRMVI.FCRMVI_CODCPT, FCRMVI.FCRMVI_PRECIO "
                    + " HAVING (SUM(FCRMVI.FCRMVI_CANTID) > 0) "
                    + " ORDER BY NROCTA, TIPPRO, ARTCOD, PRECIO ";

            Query q = em.createNativeQuery(sQuery, ProductoPendiente.class);

            return (ProductoPendiente) q.getSingleResult();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error al obtener pendientes por producto");
            return null;
        }
    }

    public List<PedidoProducto> getPendientePorPedidoProducto(String nrocta, String tippro, String artcod) {
        
        try {
            String sQuery = " SELECT dbo.FCRMVH.FCRMVH_MODFOR + dbo.FCRMVH.FCRMVH_CODFOR + CONVERT(VARCHAR, dbo.FCRMVH.FCRMVH_NROFOR) + CONVERT(VARCHAR, dbo.FCRMVI.FCRMVI_TIPORI) + CONVERT(VARCHAR, dbo.FCRMVI.FCRMVI_ARTORI) AS ID, dbo.FCRMVH.FCRMVH_NROCTA AS NROCTA, "
                    + " dbo.FCRMVI.FCRMVI_TIPORI AS TIPPRO, dbo.FCRMVI.FCRMVI_ARTORI AS ARTCOD, dbo.FCRMVH.FCRMVH_CODFOR AS CODFOR, "
                    + " dbo.FCRMVH.FCRMVH_MODFOR AS MODFOR, dbo.FCRMVH.FCRMVH_NROFOR AS NROFOR, dbo.FCRMVH.FCRMVH_SUCURS AS SUCURS, "
                    + " dbo.FCRMVH.FCRMVH_FCHMOV AS FCHMOV, ISNULL(dbo.FCRMVI.FCRMVI_PRECIO, 0) AS PRECIO, ISNULL"
                    + " ((SELECT     GRCCOI_PORCEN"
                    + " FROM         dbo.GRCCOI"
                    + " WHERE     (GRCCOI_MODCPT = dbo.FCRMVI.FCRMVI_MODCPT) AND (GRCCOI_TIPCPT = dbo.FCRMVI.FCRMVI_TIPCPT) AND (GRCCOI_TIPIMP = 'IVA') AND "
                    + "      (GRCCOI_CODCPT = dbo.FCRMVI.FCRMVI_CODCPT)), 0) AS IVA, SUM(dbo.FCRMVI.FCRMVI_CANTID) AS PENDIENTE, "
                    + " SUM(dbo.FCRMVI.FCRMVI_CANTID * dbo.FCRMVI.FCRMVI_PRECIO + dbo.FCRMVI.FCRMVI_CANTID * dbo.FCRMVI.FCRMVI_PRECIO * dbo.FCRMVI.FCRMVI_PCTBFN / 100) "
                    + " AS IMPORTE_TOTAL "
                    + " FROM  dbo.FCRMVH INNER JOIN"
                    + "      dbo.FCRMVI ON dbo.FCRMVH.FCRMVH_MODFOR = dbo.FCRMVI.FCRMVI_MODAPL "
                    + "      AND dbo.FCRMVH.FCRMVH_CODFOR = dbo.FCRMVI.FCRMVI_CODAPL "
                    + "      AND dbo.FCRMVH.FCRMVH_NROFOR = dbo.FCRMVI.FCRMVI_NROAPL"
                    + " WHERE (dbo.FCRMVH.FCRMVH_CIRCOM = '0200') "
                    + " AND FCRMVH.FCRMVH_NROCTA = '"+nrocta+"' "
                    + " AND FCRMVI.FCRMVI_TIPPRO = '"+tippro+"' "
                    + " AND FCRMVI.FCRMVI_ARTCOD = '"+artcod+"' "
                    + " GROUP BY dbo.FCRMVH.FCRMVH_NROCTA,  "
                    + " dbo.FCRMVI.FCRMVI_TIPORI, dbo.FCRMVI.FCRMVI_ARTORI, "
                    + " dbo.FCRMVI.FCRMVI_MODCPT, dbo.FCRMVI.FCRMVI_TIPCPT, "
                    + " dbo.FCRMVI.FCRMVI_CODCPT, dbo.FCRMVH.FCRMVH_CODFOR, "
                    + " dbo.FCRMVH.FCRMVH_MODFOR, dbo.FCRMVH.FCRMVH_NROFOR, "
                    + " dbo.FCRMVH.FCRMVH_SUCURS, dbo.FCRMVH.FCRMVH_FCHMOV, "
                    + " dbo.FCRMVI.FCRMVI_PRECIO "
                    + " HAVING (SUM(dbo.FCRMVI.FCRMVI_CANTID) > 0)"
                    + " ORDER BY NROCTA, TIPPRO, ARTCOD, PRECIO";

            Query q = em.createNativeQuery(sQuery, PedidoProducto.class);

            return q.getResultList();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error al obtener pendientes por pedido y producto");
            return null;
        }
    }

    public FC_ItemAplicacion getItemAplicacion(FC_ItemMovimientoPK idPK) {

        return getObjeto(FC_ItemAplicacion.class, idPK);
    }

 
}
